﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using PromotionApp.Model;
using System.Data;
using PromotionApp.DAL;
using System.Text;
using PromotionApp.Utility;

namespace PromotionApp.BLL
{
    public class CategoryBLL
    {
        #region Private Variable

        private static CategoryBLL _CategoryBLL = null;

        #endregion

        #region Constructor

        private CategoryBLL()
        {
        }

        #endregion

        #region Private Method

        private string GetCategorySummaryDomainCondition(ref bool x, string strDomainName)
        {
            string strCondition = string.Empty;

            if (strDomainName.Length > 0)
                strCondition = string.Format(" {0} a.domainname = '{1}'", DynamicQueryHelper.GetConditionPrefix(ref x), strDomainName);

            return strCondition;
        }

        private string GetCategorySummaryCategoryCondition(ref bool x, int categoryId)
        {
            string strCondition = string.Empty;

            if (categoryId > 0)
                strCondition = string.Format(" {0} b.categoryid = '{1}'",DynamicQueryHelper.GetConditionPrefix(ref x) , categoryId);

            return strCondition;
        }

        private string GetCategorySummarySql(string strDomainName, int categoryId, string strStartDate, string strEndDate)
        {
            StringBuilder sb = new StringBuilder();
            bool blnConditionFlag = false;

            sb.Append(" with temp as");
            sb.Append(" (");           
            sb.Append(" select a.[DomainName]");
            sb.Append(" , a.[CategoryId]");
            sb.Append(" , a.[CategoryName]");
            sb.Append(" , b.[StatusCode]");
            sb.Append(" , count(*) as [NumOfTopic]");
            sb.Append(" from [dbo].[mscategory] as a");
            sb.Append(" left join [dbo].[txtopic] as b on a.[categoryid] = b.[categoryid]");
            sb.AppendFormat(" where b.[PostedDate] between convert(datetime, '{0} 00:00:00', 103) and convert(datetime, '{1} 23:59:59', 103)", strStartDate, strEndDate);            
            sb.Append(" group by a.[domainname], a.[categoryid], a.[categoryname], b.[statuscode]");
            sb.Append(" )");            
            sb.Append(" SELECT a.[DomainName]");
	        sb.Append(" , b.[CategoryId]");		
	        sb.Append(" , b.[CategoryName]");
	        sb.Append(" , c.[LastedPostedDate]");
	        sb.Append(" , isnull(d.[StatusCode], '') as [StatusCode]");
	        sb.Append(" , isnull(d.[NumOftopic], 0) as [NumOfTopic]");	   
            sb.Append(" FROM [dbo].[LkDomain] as a");
	        sb.Append(" inner join dbo.MsCategory as b on a.[DomainName] = b.[DomainName]");
	        sb.Append(" inner join [dbo].[mscategorylastedpost] as c on b.[categoryid] = c.[categoryid]");
	        sb.Append(" left join temp as d on b.[DomainName] = d.[DomainName] and b.CategoryId = d.CategoryId");            
            sb.Append(GetCategorySummaryDomainCondition(ref blnConditionFlag, strDomainName));
            sb.Append(GetCategorySummaryCategoryCondition(ref blnConditionFlag, categoryId));
            sb.Append(" order by a.[DomainName] asc, b.[CategoryName] asc");                                   

            return sb.ToString();
        }

        private string GetSearchCategoryDomainNameCondition(ref bool blnConditionFlag, string strDomainName)
        {
            string strCondition = string.Empty;

            if (strDomainName.Length > 0)
                strCondition = string.Format(" {0} a.[domainname] = '{1}'", DynamicQueryHelper.GetConditionPrefix(ref blnConditionFlag), strDomainName);

            return strCondition;
        }

        private string GetSearchCategoryCategoryNameCondition(ref bool blnConditionFlag, string strCategoryName)
        {
            string strCondition = string.Empty;

            if (strCategoryName.Length > 0)
                strCondition = string.Format(" {0} a.[categoryname] like '%{1}%'", DynamicQueryHelper.GetConditionPrefix(ref blnConditionFlag), strCategoryName);

            return strCondition;
        }

        private string GetSearchCategoryStatusCodeCondition(ref bool blnConditionFlag, string strStatusCode)
        {
            string strCondition = string.Empty;

            if (strStatusCode.Length > 0)
                strCondition = string.Format(" {0} a.[statuscode] = '{1}'", DynamicQueryHelper.GetConditionPrefix(ref blnConditionFlag), strStatusCode);

            return strCondition;
        }

        private string GetSearchCategoryCTESql(string strDoamainName, string strCategoryName, string strStatusCode)
        {
            StringBuilder sb = new StringBuilder();
            bool blnConditionFlag = false;

            sb.Append(" with temp as");
            sb.Append(" (");
            sb.Append(" select row_number() over(order by a.[categoryid] asc) as [no]");
            sb.Append(" ,a.[categoryid]");
            sb.Append(" ,a.[categoryname]");
            sb.Append(" ,a.[domainname]");
            sb.Append(" ,a.[languagecode]");
            sb.Append(" , isnull(b.[languagename], '') as [languagename]");
            sb.Append(" ,a.[statuscode]");
            sb.Append(" , isnull(c.statusname, '') as [statusname]");
            sb.Append(" ,a.[createdby]");
            sb.Append(" , isnull(d.userName, '') as [createdbyname]");
            sb.Append(" ,a.[createddate]");
            sb.Append(" ,a.[updateddate]");
            sb.Append(" ,a.[updatedby]");
            sb.Append(" , isnull(e.username, '') as [updatedbyname]");
            sb.Append(" FROM [dbo].[mscategory] as a");
            sb.Append(" left join dbo.[lklanguage] as b on a.[languagecode] = b.[languagecode]");
            sb.Append(" left join dbo.[lkstatus] as c on a.[statuscode] = c.[statuscode]");
            sb.Append(" left join dbo.[msinternaluser] as d on a.[createdby] = d.[userid]");
            sb.Append(" left join dbo.[msinternaluser] as e on a.[updatedby] = e.[userid]");
            sb.Append(GetSearchCategoryDomainNameCondition(ref blnConditionFlag, strDoamainName));
            sb.Append(GetSearchCategoryCategoryNameCondition(ref blnConditionFlag, strCategoryName));
            sb.Append(GetSearchCategoryStatusCodeCondition(ref blnConditionFlag, strStatusCode));
            sb.Append(" )");

            return sb.ToString();
        }

        private string GetSearchCategorySql(string strCTEQuery, int pageSize, int pageNumber)
        {
            StringBuilder sb = new StringBuilder();

            sb.Append(strCTEQuery);
            sb.Append(" select [No], [CategoryId], [CategoryName], [DomainName], [LanguageCode], [LanguageName], [StatusCode], [StatusName], [CreatedBy], [CreatedByName], [CreatedDate], [UpdatedDate], [UpdatedBy], [UpdatedByName]");
            sb.Append(" from temp");
            sb.AppendFormat(" where [No] between {0} and {1}", DynamicQueryHelper.GetStartNo(pageSize, pageNumber), DynamicQueryHelper.GetEndNo(pageSize, pageNumber));

            return sb.ToString();
        }

        private string GetSearchCategorySqlCountSql(string strCTEQuery)
        {
            StringBuilder sb = new StringBuilder();

            sb.Append(strCTEQuery);
            sb.Append(" select count(*) as [RowCount]");
            sb.Append(" from temp");

            return sb.ToString();
        }

        #endregion

        #region Public Method

        public List<CategoryStatistic> GetSummaryStat()
        {
            List<CategoryStatistic> list = new List<CategoryStatistic>();
            DataSet ds = CategoryDAL.GetInstance().GetSummaryStat(ConfigurationHelper.DomainName);

            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow r in ds.Tables[0].Rows)
                {
                    CategoryStatistic o = new CategoryStatistic();
                    o.CategoryId = Convert.ToInt32(r["CategoryId"]);
                    o.CategoryName = r["CategoryName"].ToString().Trim();
                    o.NumTopics = Convert.ToInt32(r["NumTopics"]);
                    o.NumReplies = Convert.ToInt32(r["NumReplies"]);
                    o.LastedPostedDate = Convert.ToDateTime(r["LastedPostedDate"]);
                    list.Add(o);
                }
            }

            return list;
        }

        public void Update(Category category)
        {
            int count = CategoryDAL.GetInstance().CountByCategoryNameUpdate(category.CategoryId, category.CategoryName);

            if (count == 0)
                CategoryDAL.GetInstance().Update(category);
            else
                throw new ApplicationException("Duplicate category name");            
        }

        public void Add(Category category)
        {
            int count = CategoryDAL.GetInstance().CountByCategoryNameInsert(category.CategoryName);

            if (count == 0)
                CategoryDAL.GetInstance().Add(category);
            else
                throw new ApplicationException("Duplicate category name");
        }

        public void Delete(int categoryId)
        {
            CategoryDAL.GetInstance().Delete(categoryId);
        }

        public List<Category> SearchCategory(CategoryPagingArgs e)
        {
            List<Category> list = new List<Category>();
            string strCTESql = GetSearchCategoryCTESql(e.DomainName, e.CategoryName, e.StatusCode);
            string strSearchSql = GetSearchCategorySql(strCTESql, e.PageSize, e.PageNumber);
            string strCountQuery = GetSearchCategorySqlCountSql(strCTESql);

            e.Total = CategoryDAL.GetInstance().GetTotalRow(strCountQuery);
            DynamicQueryHelper.GetMaxPageNumber(strCountQuery, e);
            DataSet ds = CategoryDAL.GetInstance().GetDataSetFromSql(strSearchSql);

            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow r in ds.Tables[0].Rows)
                {
                    Category o = new Category();
                    o.No = Convert.ToInt32(r["No"]);
                    o.CategoryId = Convert.ToInt32(r["CategoryId"]);
                    o.CategoryName = r["CategoryName"].ToString().Trim();
                    o.DomainName = r["DomainName"].ToString().Trim();
                    o.LanguageCode = r["LanguageCode"].ToString().Trim();
                    o.LanguageName = r["LanguageName"].ToString().Trim();
                    o.StatusCode = r["StatusCode"].ToString().Trim();
                    o.StatusName = r["StatusName"].ToString().Trim();
                    o.CreatedBy = Convert.ToInt32(r["CreatedBy"]);
                    o.CreatedByName = r["CreatedByName"].ToString().Trim();
                    o.CreatedDate = Convert.ToDateTime(r["CreatedDate"]);
                    o.UpdatedDate = Convert.ToDateTime(r["UpdatedDate"]);
                    o.UpdatedBy = Convert.ToInt32(r["UpdatedBy"]);
                    o.UpdatedByName = r["UpdatedByName"].ToString().Trim();
                    list.Add(o);
                }
            }

            return list;
        }

        public Category GetCategoryById(int categoryId)
        {
            Category o = new Category();
            DataSet ds = CategoryDAL.GetInstance().GetCatgoryById(categoryId);

            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                DataRow r = ds.Tables[0].Rows[0];                
                o.CategoryId = Convert.ToInt32(r["CategoryId"]);
                o.CategoryName = r["CategoryName"].ToString().Trim();
                o.DomainName = r["DomainName"].ToString().Trim();
                o.LanguageCode = r["LanguageCode"].ToString().Trim();                
                o.StatusCode = r["StatusCode"].ToString().Trim();                
                o.CreatedBy = Convert.ToInt32(r["CreatedBy"]);                
                o.CreatedDate = Convert.ToDateTime(r["CreatedDate"]);
                o.UpdatedDate = Convert.ToDateTime(r["UpdatedDate"]);
                o.UpdatedBy = Convert.ToInt32(r["UpdatedBy"]);                
            }            

            return o;
        }

        public List<string> GetAllDomain()
        {
            List<string> list = new List<string>();
            DataSet ds = CategoryDAL.GetInstance().GetAllDomain();

            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow r in ds.Tables[0].Rows)
                {
                    list.Add(r["DomainName"].ToString().Trim());
                }
            }            

            return list;
        }

        public List<Category> GetCategoryByDomainName(string strDomainName)
        {
            List<Category> list = new List<Category>();
            DataSet ds = CategoryDAL.GetInstance().GetCategoryByDomainName(strDomainName);

            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow r in ds.Tables[0].Rows)
                {
                    Category o = new Category();
                    o.CategoryId = Convert.ToInt32(r["CategoryId"]);
                    o.CategoryName = r["CategoryName"].ToString().Trim();
                    o.DomainName = r["DomainName"].ToString().Trim();
                    o.LanguageCode = r["LanguageCode"].ToString().Trim();
                    o.StatusCode = r["StatusCode"].ToString().Trim();
                    o.CreatedBy = Convert.ToInt32(r["CreatedBy"]);
                    o.CreatedDate = Convert.ToDateTime(r["CreatedDate"]);
                    o.UpdatedDate = Convert.ToDateTime(r["UpdatedDate"]);
                    list.Add(o);
                }
            }            

            return list;
        }

        public List<CategorySummary> GetCategorySummaryList(string strDomainName, int categoryId, string strStartDate, string strEndDate)
        {
            List<CategorySummary> list = new List<CategorySummary>();
            string strSql = GetCategorySummarySql(strDomainName, categoryId, strStartDate, strEndDate);
            DataSet ds = CategoryDAL.GetInstance().GetCategorySummaryFromDb(strSql);
            int temp = 0;

            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                CategorySummary o = null;

                foreach (DataRow r in ds.Tables[0].Rows)
                {
                    int id = Convert.ToInt32(r["CategoryId"]);
                    string strStatusCode = r["StatusCode"].ToString().Trim();

                    if (temp != id)
                    {
                        temp = id;
                        o = new CategorySummary();
                        list.Add(o);
                        o.DomainName = r["DomainName"].ToString().Trim();
                        o.CategoryId = id;
                        o.CategoryName = r["CategoryName"].ToString().Trim();
                        o.LastPostedDate = Convert.ToDateTime(r["LastedPostedDate"]);
                    }

                    if (strStatusCode.Equals("E"))
                        o.NumEnable = Convert.ToInt32(r["NumOfTopic"]);
                    else if (strStatusCode.Equals("P"))
                        o.NumPending = Convert.ToInt32(r["NumOfTopic"]);
                    else if (strStatusCode.Equals("D"))
                        o.NumDisable = Convert.ToInt32(r["NumOfTopic"]);
                }
            }            

            return list;
        }

        public static CategoryBLL GetInstance()
        {
            if (_CategoryBLL == null)
                _CategoryBLL = new CategoryBLL();

            return _CategoryBLL;
        }

        #endregion
    }
}